package cn.kys.modules.system.service.impl;


import cn.kys.modules.system.repository.ToolsResposity;
import cn.kys.modules.system.service.CaseService;
import cn.kys.modules.system.service.GetDeptIdByLoginUser;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Primary
@Service
@Slf4j
public class CaseServiceImplzxw implements CaseService {
    @Autowired
    private ToolsResposity toolsResposity;

    private SimpleDateFormat sdf_ = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");


    @Autowired
    private GetDeptIdByLoginUser getDeptIdByLoginUser;
    @Value("${adminUserIncludeStr}")
    private String adminUserIncludeStr;

    // 加部门权限的id
    public Long getDeptId() {
        return getDeptIdByLoginUser.getDeptId();
    }

    // 加部门权限的用户名
    public String getUserName() {
        return getDeptIdByLoginUser.getUserName();
    }


    @Override
    public Map addCase(String name, boolean is_public) {
        String create_time = sdf_.format(new Date());
        Map returnMap = new HashMap();

        String sql = String.format("SELECT * FROM public.t_case where name = '%s' and dept_id=%d", name, getDeptId());
        Map caseMap = toolsResposity.exeSql(sql);
        if (caseMap == null) {
            String sql_insert = "INSERT INTO public.t_case(name,is_public,create_time,dept_id) VALUES ('" + name + "'," + is_public + ",'" + create_time + "'," + getDeptId() + ")";
//            String sql_insert =String.format("INSERT INTO public.t_case(name,is_public,create_time) VALUES ('" + name + "'," +is_public+",'"+create_time+"')) ";
            toolsResposity.exeSql(sql_insert);
            returnMap.put("success", true);
        } else {
            returnMap.put("success", false);
            returnMap.put("msg", "表单项目已经存在");
        }

        return returnMap;
    }

    /**
     * @return
     * @Author zxw
     * @Description //TODO  修改原代码 添加权限
     * @Date 2023/12/4  9:17
     **/
    @Override
    public Map editCase(String id, String name, String caseformids, String casebaselayerids, String casespeciallayerids, String caseeditlayerid) {
        Map resultMap = new HashMap();

        List<Map> nameList = toolsResposity.queryNameFromCase();
        for (Map map : nameList) {
            //判断更新的是不是同一条，是的话
            String idStr = map.get("id").toString();
            String nameStr = map.get("name").toString();
            if (!id.equals(idStr) && name.equals(nameStr)) {
                resultMap.put("success", false);
                resultMap.put("msg", name + "已经存在");
                return resultMap;
            }
        }

        //        String sql = "UPDATE t_case SET name = '" + name + "' WHERE id ='" + id + "'";
        String sql = String.format("UPDATE t_case SET name = '%s' WHERE id ='%s' and dept_id='%s'", name, id, getDeptId());
        toolsResposity.exeSql(sql);

        //更新t_form_content表,先删除，再插入
        //删除
//        String sql_delete = "delete from t_case_form where caseid ="+id;
        String sql_delete = String.format("delete from t_case_form where caseid ='%s' and dept_id='%s'", id, getDeptId());
        toolsResposity.exeSql(sql_delete);
        //更新
        if (!"".equals(caseformids)) {
            String caseformidArr[] = caseformids.split(",");
            for (int i = 0; i < caseformidArr.length; i++) {
                String caseformid = caseformidArr[i];
//                String sql_caseform = "select * from t_form where id = "+caseformid;
                String sql_caseform = String.format("select * from t_form  WHERE id ='%s' and dept_id='%s'", caseformid, getDeptId());
                Map caseformtMap = toolsResposity.exeSql(sql_caseform);
                if (caseformtMap != null) {
                    String formid = caseformtMap.get("id").toString();
                    String formname = caseformtMap.get("name").toString();
//                    String sql_insert = "INSERT INTO public.t_case_form(caseid,formid,casename,formname) VALUES (" + id +",'"+ formid +"','"+ name +"','"+ formname +"');";
                    String sql_insert = String.format("INSERT INTO public.t_case_form(caseid,formid,casename,formname,dept_id) VALUES ('%s','%s','%s','%s','%s')", id, formid, name, formname, getDeptId());
                    toolsResposity.exeSql(sql_insert);
                }

            }
        }

        //删除
//        String sql_case_baselayer_delete = "delete from t_case_baselayer where caseid ="+id;
        String sql_case_baselayer_delete = String.format("delete from t_case_baselayer where caseid ='%s' and dept_id='%s'", id, getDeptId());
        toolsResposity.exeSql(sql_case_baselayer_delete);
        //更新t_case_baselayer
        if (!"".equals(casebaselayerids)) {
            String caselayeridArr[] = casebaselayerids.split(",");
            for (int i = 0; i < caselayeridArr.length; i++) {
                String caselayerid = caselayeridArr[i];
//                String sql_caselayer = "select * from t_layer where id = "+caselayerid;
                String sql_caselayer = String.format("select * from t_layer where id ='%s' and dept_id='%s'", caselayerid, getDeptId());
                Map caselayerMap = toolsResposity.exeSql(sql_caselayer);
                if (caselayerMap != null) {
                    String layerid = caselayerMap.get("id").toString();
                    String layername = caselayerMap.get("name").toString();
                    String layercname = caselayerMap.get("cname").toString();
                    String type = caselayerMap.get("type").toString();
                    String url = "";
                    if (caselayerMap.get("url") != null) {
                        url = caselayerMap.get("url").toString();
                    }
//                    String sql_insert = "INSERT INTO public.t_case_baselayer(caseid,layerid,casename,layername,url,type) VALUES (" + id +",'"+ layerid +"','"+ name +"','"+ layername +"','"+url+"','"+type+"');";
                    String sql_insert = String.format("INSERT INTO public.t_case_baselayer(caseid,layerid,casename,layername,url,type,dept_id) VALUES ('%s','%s','%s','%s','%s','%s','%s')", id, layerid, name, layername, url, type, getDeptId());
                    toolsResposity.exeSql(sql_insert);
                }

            }
        }
        //删除t_case_speciallayer
//        String sql_case_speciallayer_delete = "delete from t_case_speciallayer where caseid ="+id;
        String sql_case_speciallayer_delete = String.format("delete from t_case_speciallayer where caseid ='%s' and dept_id='%s'", id, getDeptId());
        toolsResposity.exeSql(sql_case_speciallayer_delete);
        //更新
        if (!"".equals(casespeciallayerids)) {
            String caselayeridArr[] = casespeciallayerids.split(",");
            for (int i = 0; i < caselayeridArr.length; i++) {
                String caselayerid = caselayeridArr[i];
//                String sql_caselayer = "select * from t_layer where id = "+caselayerid;
                String sql_caselayer = String.format("select * from t_layer where id = '%s' and dept_id='%s'", caselayerid, getDeptId());
                Map caselayerMap = toolsResposity.exeSql(sql_caselayer);
                if (caselayerMap != null) {
                    String layerid = caselayerMap.get("id").toString();
                    String layername = caselayerMap.get("name").toString();
                    String layercname = caselayerMap.get("cname").toString();
                    String type = caselayerMap.get("type").toString();
                    String url = "";
                    if (caselayerMap.get("url") != null) {
                        url = caselayerMap.get("url").toString();
                    }
//                    String sql_insert = "INSERT INTO public.t_case_speciallayer(caseid,layerid,casename,layername,url,type) VALUES (" + id +",'"+ layerid +"','"+ name +"','"+ layername +"','"+url+"','"+type+"');";
                    String sql_insert = String.format("INSERT INTO public.t_case_speciallayer(caseid,layerid,casename,layername,url,type,dept_id) VALUES ('%s','%s','%s','%s','%s','%s','%s')", id, layerid, name, layername, url, type, getDeptId());
                    toolsResposity.exeSql(sql_insert);
                }

            }
        }

        //删除
//        String sql_case_editlayer_delete = "delete from t_case_editlayer where caseid ="+id;
        String sql_case_editlayer_delete = String.format("delete from t_case_editlayer where caseid ='%s' and dept_id='%s'", id, getDeptId());
        toolsResposity.exeSql(sql_case_editlayer_delete);
        //更新
        if (!"".equals(caseeditlayerid)) {
//            String sql_caseeditlayer = "select * from t_layer where id = "+caseeditlayerid;
            String sql_caseeditlayer = String.format("select * from t_layer where id = '%s' and dept_id='%s'", caseeditlayerid, getDeptId());
            Map caseeditlayerMap = toolsResposity.exeSql(sql_caseeditlayer);
            String layerid = caseeditlayerMap.get("id").toString();
            String layername = caseeditlayerMap.get("name").toString();
            String layercname = caseeditlayerMap.get("cname").toString();
            String type = caseeditlayerMap.get("type").toString();
            String url = "";
            if (caseeditlayerMap.get("url") != null) {
                url = caseeditlayerMap.get("url").toString();
            }

//            String sql_insert = "INSERT INTO public.t_case_editlayer(caseid,layerid,casename,layername,url,type) VALUES (" + id +",'"+ layerid +"','"+ name +"','"+ layername +"','"+url+"','"+type+"');";
            String sql_insert = String.format("INSERT INTO public.t_case_editlayer(caseid,layerid,casename,layername,url,type,dept_id) VALUES ('%s','%s','%s','%s','%s','%s','%s')", id, layerid, name, layername, url, type, getDeptId());
            toolsResposity.exeSql(sql_insert);
        }

        resultMap.put("success",true);
        resultMap.put("msg", "编辑成功");
        return resultMap;
    }

    @Override
    public List getAllCases() {
        String sql = "";
        if (getUserName().contains(adminUserIncludeStr)) {
            sql = String.format("SELECT * FROM public.t_case");
        } else {
            sql = String.format("SELECT * FROM public.t_case where dept_id=%d", getDeptId());
        }
        return toolsResposity.exeSqlList(sql);
    }

    /*@Override
    public Map delCaseById(String id) {
        Map returnMap = new HashMap();

        String sql_case_form = "select * from t_case_form where caseid in (" + id + ")";
        List caseContentMap = toolsResposity.exeSqlList(sql_case_form);
        String sql_case_layer = "select * from t_case_layer where caseid in (" + id + ")";
        List caseLayerMap = toolsResposity.exeSqlList(sql_case_layer);

        if (caseContentMap.size() > 0||caseLayerMap.size()>0) {
            //不能删
            returnMap.put("deleted", false);
            returnMap.put("msg", "该方案表已被使用，不能删除");
        } else {
            //可以删除,删除t_case
            String sql_delete = "delete from t_case where id in (" + id + ")";
            toolsResposity.exeSql(sql_delete);

            //删除t_case_layer
            String sql_case_layer_delete = "delete from t_case_layer where caseid in (" + id + ")";
            toolsResposity.exeSql(sql_case_layer_delete);

            //删除t_case_form
            String sql_case_form_delete = "delete from t_case_form where caseid in (" + id + ")";
            toolsResposity.exeSql(sql_case_form_delete);

            returnMap.put("deleted", true);
        }

        return returnMap;
    }*/

    @Override
    public Map delCaseById(String id) {
        Map returnMap = new HashMap();

        String sql_task = "select * from t_task where caseid in (" + id + ") and is_del = 0";
        List taskMap = toolsResposity.exeSqlList(sql_task);


        if (!taskMap.isEmpty()) {
            //不能删
            returnMap.put("deleted", false);
            returnMap.put("msg", "该方案表已被使用，不能删除");
        } else {
            //可以删除,删除t_case
            String sql_delete = "delete from t_case where id in (" + id + ")";
            toolsResposity.exeSql(sql_delete);

            //删除t_case_baselayer
            String sql_case_baselayerlayer_delete = "delete from t_case_baselayer where caseid in (" + id + ")";
            toolsResposity.exeSql(sql_case_baselayerlayer_delete);

            //删除t_case_speciallayer
            String sql_case_speciallayer_delete = "delete from t_case_speciallayer where caseid in (" + id + ")";
            toolsResposity.exeSql(sql_case_speciallayer_delete);

            //删除t_case_editlayer
            String sql_case_editlayer_delete = "delete from t_case_editlayer where caseid in (" + id + ")";
            toolsResposity.exeSql(sql_case_editlayer_delete);

            //删除t_case_form
            String sql_case_form_delete = "delete from t_case_form where caseid in (" + id + ")";
            toolsResposity.exeSql(sql_case_form_delete);


            returnMap.put("deleted", true);
        }

        return returnMap;
    }

    @Override
    public Map getBindingByCaseId(String id) {

        String sql_case_form = "SELECT * FROM public.t_case_form where caseid = " + id;
        List caseFormList = toolsResposity.exeSqlList(sql_case_form);

        String sql_case_baselayer = "SELECT * FROM public.t_case_baselayer where caseid = " + id;
        List casebaseLayerList = toolsResposity.exeSqlList(sql_case_baselayer);

        String sql_case_speciallayer = "SELECT * FROM public.t_case_speciallayer where caseid = " + id;
        List casespecialLayerList = toolsResposity.exeSqlList(sql_case_speciallayer);

        String sql_case_editlayer = "SELECT * FROM public.t_case_editlayer where caseid = " + id;
        List caseEditLayerList = toolsResposity.exeSqlList(sql_case_editlayer);

        Map returnMap = new HashMap();
        returnMap.put("form", caseFormList);
        returnMap.put("baselayer", casebaseLayerList);
        returnMap.put("special", casespecialLayerList);
        returnMap.put("editlayer", caseEditLayerList);
        return returnMap;
    }

    /**
     * @return
     * @Author zxw
     * @Description //TODO   修改源代码bug  :  没有添加权限
     * @Date 2023/12/4  9:06
     **/
    @Override
    public Map getLayersByCaseId(String caseid) {

//        String sql_case_baselayer = "SELECT * FROM public.t_case_baselayer where caseid = "+caseid;
        String sql_case_baselayer = String.format("SELECT * FROM public.t_case_baselayer where caseid = '%s' and dept_id='%s'", caseid, getDeptId());
        List casebaseLayerList = toolsResposity.exeSqlList(sql_case_baselayer);

//        String sql_case_speciallayer = "SELECT * FROM public.t_case_speciallayer where caseid = "+caseid;
        String sql_case_speciallayer = String.format("SELECT * FROM public.t_case_speciallayer where caseid = '%s' and dept_id='%s'", caseid, getDeptId());
        List casespecialLayerList = toolsResposity.exeSqlList(sql_case_speciallayer);

//        String sql_case_editlayer = "SELECT * FROM public.t_case_editlayer where caseid = "+caseid;
        String sql_case_editlayer = String.format("SELECT * FROM public.t_case_editlayer where caseid = '%s' and dept_id='%s'", caseid, getDeptId());
        Map caseEditLayerMap = toolsResposity.exeSql(sql_case_editlayer);

//        String layername = null!=caseEditLayerMap?caseEditLayerMap.get("layername").toString():"";
        String layername = caseEditLayerMap.get("layername").toString();

        String sql = "SELECT st_astext(ST_Transform(geom,4326)) FROM " + layername;
        List editListPolygon = toolsResposity.exeSqlList(sql);

        Map returnMap = new HashMap();
        returnMap.put("baselayer", casebaseLayerList);
        returnMap.put("special", casespecialLayerList);
        returnMap.put("editLayerPolygon", filterGeo(editListPolygon));
        return returnMap;
    }

    private List filterGeo(List list) {
        List returnList = new ArrayList();
        for (int i = 0; i < list.size(); i++) {
            Map map = (Map) list.get(i);
            List listPolygon = new ArrayList();

            String st_astext = (String) map.get("st_astext");
            if (st_astext.contains("MULTIPOLYGON")) {
                st_astext = st_astext.substring(15, st_astext.length() - 3);

                String[] polygons = st_astext.split("\\)\\),\\(\\(");

                for (int j = 0; j < polygons.length; j++) {
                    String polygon = polygons[j];
                    if (polygon.contains("),(")) {
                        String[] polygonHole = polygon.split("\\),\\(");
                        listPolygon.add(polygonHole);
                    } else {
                        listPolygon.add(polygon);
                    }
                }

                map.put("geo", listPolygon);

            } else if (st_astext.contains("POLYGON")) {
                st_astext = st_astext.substring(9, st_astext.length() - 2);
                map.put("geo", st_astext);
            } else {
                st_astext = st_astext.substring(6, st_astext.length() - 2);
                map.put("geo", st_astext);
            }


            returnList.add(map);
        }
        return returnList;
    }

    @Override
    public List getCasesByParam(boolean is_public) {

        String sql = "";
        if (getUserName().contains(adminUserIncludeStr)) {
            sql = String.format("SELECT * FROM public.t_case where is_public = %b order by create_time desc", is_public);
        } else {
            sql = String.format("SELECT * FROM public.t_case where is_public = %b and dept_id='%s'", is_public, getDeptId());
        }

////        String sql = "SELECT * FROM public.t_case where is_public = "+is_public;
//        String sql =String.format("SELECT * FROM public.t_case where is_public = %b and dept_id=%d",is_public,getDeptId());
//        return toolsResposity.exeSqlList(sql);

        return toolsResposity.exeSqlList(sql);
    }




    /**
     * @Description //TODO 导入shp对应的表名和权限分配  删除
     */
    public void delTableDept(String id) {
        String insertSQL = String.format("DELETE FROM t_table_dept WHERE id='%s'", id);
        toolsResposity.exeSqlList(insertSQL);
    }

    /**
     * @Description //TODO 导入shp对应的表名和权限分配  查找
     */
    public List queryTableDept() {
        String insertSQL = String.format("SELECT * FROM t_table_dept WHERE  dept_id='%s'", getDeptId());
        return toolsResposity.exeSqlList(insertSQL);
    }

    /**
     * @Description //TODO 导入shp对应的表名和权限分配  编辑
     */
    public void editTableDept(String tablename) {
        String insertSQL = String.format("UPDATE t_table_dept SET tablename='%s' WHERE  dept_id='%s'", tablename, getDeptId());
        toolsResposity.exeSqlList(insertSQL);
    }

    /**
     * @Description //TODO 导入shp对应的表名和权限分配  查找  吕剑锋想法
     */
    public List<String> queryTableDeptUnDistribute() {

        //原来数据库的所有表，写死——吕剑锋想法
        List rawDBlist = Arrays.asList("t_case_speciallayer", "spatial_ref_sys", "station", "sys_layer", "sys_role", "sys_user", "sys_user_layer", "sys_user_role", "t_card", "t_case_baselayer", "t_case_form",
                                       "t_case_editlayer", "t_contenttype", "change", "e123", "email_config", "fieldvalue_dictionary", "log", "lw11", "lw12", "lw123", "lyxb", "job", "menu", "roles_depts", "roles_menus",
                                       "role", "sick_tree", "t_subtask", "t_gps", "t_form_content",
                                       "t_subtask_geom", "t_subtask_polygon", "t_task", "t_form", "t_task_user", "t_formcontent", "t_layer", "tree11", "tree12", "tree123", "user_avatar", "users_roles", "verification_code",
                                       "visits", "wys_polygon_4326_dc", "dict", "dict_detail",
                                       "project", "project_detail", "dept", "tuser", "t_case", "t_video_info", "t_table_dept");

        //检测数据库中所有表（导入新的shp后）
        String sql = String.format("SELECT tablename FROM pg_tables  WHERE schemaname = 'public'");
        List<Map> allTempTableList = toolsResposity.exeSqlList(sql);
        List<String> allTableList = new ArrayList<>();
        for (Map map : allTempTableList) {
            if (null != map) {
                allTableList.add(map.get("tablename").toString());
            }
        }
        //t_table_dept表中待绑定的表
        String queryTableDept = String.format("SELECT id, tablename, dept_id FROM t_table_dept");
        List<Map> tempTableDeptList = toolsResposity.exeSqlList(queryTableDept);
        List<String> tableDeptList = new ArrayList<>();
        for (Map map : tempTableDeptList) {
            if (null != map) {
                tableDeptList.add(map.get("tablename").toString());
            }
        }

        //添加shp数据新增的shp表——
        List<String> insertNewShpList = allTableList.stream()
                .filter(e -> !rawDBlist.contains(e))
                .collect(Collectors.toList());
        log.info("添加shp数据新增的shp表：{}", insertNewShpList);

        //添加shp数据，但是未分配的shp表
        List<String> result = insertNewShpList.stream()
                .filter(e -> !tableDeptList.contains(e))
                .collect(Collectors.toList());
        log.info("添加shp数据，但是未分配的shp表：{}", result);

        return result;

    }


}

